﻿using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ESRI.ArcGIS.DataSourcesFile;
using ESRI.ArcGIS.Display;
using ESRI.ArcGIS.Editor;
using ESRI.ArcGIS.EditorExt;
using ESRI.ArcGIS.esriSystem;
using ESRI.ArcGIS.Geodatabase;
using ESRI.ArcGIS.Geometry;
using ESRI.ArcGIS.GlobeCore;
using ESRI.ArcGIS.NetworkAnalysis;
using ESRI.ArcGIS.Output;
using ESRI.ArcGIS.SystemUI;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Data;
using DIWAGIS.Modules;
using Microsoft.Office;
using DIWAGIS.Class;
using System.Reflection;
namespace DIWAGIS.ApplicationLayer.ThongKeTaiSan
{
    public partial class frmBaoCaoLapDatDongHoKH : Form
    {
        string sTableName = generalVariable.database + "." + generalVariable.schema + "." + ClassVariables.wDongHoKhachHang;
        Boolean isLoadComBox = false;
        public frmBaoCaoLapDatDongHoKH()
        {
            InitializeComponent();
        }

        private void buttonXuatBaoCao_Click(object sender, EventArgs e)
        {

            try
            {
                IFeatureWorkspace pFWS = DIWAGIS.Modules.generalVariable.ICurrentWorkspace as IFeatureWorkspace;
                ITable pTable;
                string strWhere = "";
                string sTuNgay = "";
                string sDenNgay = "";

                dateTimePickerTuNgay.CustomFormat = "yyyy/MM/dd 00:00:00";
                this.dateTimePickerTuNgay.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
                sTuNgay = dateTimePickerTuNgay.Text;

                dateTimePickerDenNgay.CustomFormat = "yyyy/MM/dd 00:00:00";
                this.dateTimePickerDenNgay.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
                sDenNgay = dateTimePickerDenNgay.Text;
                IQueryFilter pQueryfilter = new QueryFilterClass();

                pQueryfilter.WhereClause = "NgayLapDat >= '" + sTuNgay + "'" + " AND NgayLapDat <= '" + sDenNgay + "'";

                pTable = pFWS.OpenTable(sTableName);

                Excel.Application oXL;
                Excel.Workbook oWB;
                Excel.Worksheet oSheet;
                Excel.Range oRange;

                // Start Excel and get Application object. 
                oXL = new Excel.Application();

                // Set some properties 
                oXL.Visible = false;
                oXL.DisplayAlerts = false;

                // Get a new workbook. 
                oWB = oXL.Workbooks.Add(Missing.Value);

                // Get the active sheet 
                oSheet = (Excel.Worksheet)oWB.ActiveSheet;
                oSheet.Name = "Báo cáo đồng hồ khách hàng";

                int rowCount = 1;

                ICursor pCursor = pTable.Search(pQueryfilter, false) as ICursor;
                IRow pRow = (IRow)pCursor.NextRow();



                while (pRow != null)
                {
                    rowCount += 1;
                    for (int i = 1; i < pTable.Fields.FieldCount; i++)
                    {
                        // Add the header the first time through 
                        IField curField = pRow.Fields.get_Field(i - 1);
                        if (rowCount == 2)
                        {
                            oSheet.Cells[1, i] = pTable.Fields.get_Field(i - 1).AliasName;
                        }
                        if (pTable.Fields.get_Field(i - 1).Name != "SHAPE")
                        {
                            oSheet.Cells[rowCount, i] = pRow.get_Value(i - 1);
                        }
                        //oSheet.Cells[rowCount, i] = pRow.get_Value(i-1);
                    }

                    pRow = (IRow)pCursor.NextRow();
                }
                oXL.Visible = true;
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }
            catch
            {
                MessageBox.Show("Thông báo! Lỗi trong quá trình xuất báo cáo ", generalVariable.nameProgram, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        private void frmBaoCaoLapDatDongHoKH_Load(object sender, EventArgs e)
        {
            DIWAGIS.Module.WorkingWithDomain cbx = new DIWAGIS.Module.WorkingWithDomain();
            SqlCommand sqlcommand = new SqlCommand();
            sqlcommand.CommandText = "SELECT TENHUYEN,IDHUYEN FROM HUYEN ";
            cbx.loadDataToCombobox(comboBoxQuanHuyen, sqlcommand);
            isLoadComBox = true;
        }

        private void comboBoxQuanHuyen_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (isLoadComBox == true)
            {
                string strWhere = "";
                if (!string.IsNullOrEmpty(comboBoxQuanHuyen.Text))
                {
                    strWhere = comboBoxQuanHuyen.SelectedValue.ToString();
                    SqlCommand sqlcommand = new SqlCommand();
                    DIWAGIS.Module.WorkingWithDomain cbx = new DIWAGIS.Module.WorkingWithDomain();
                    //sqlcommand = new System.Data.SqlClient.SqlCommand();
                    sqlcommand.CommandText = "SELECT TenHanhChinh,IDHanhChinh FROM HANHCHINH WHERE MaHuyen = @MaHuyen";
                    sqlcommand.Parameters.AddWithValue("@MaHuyen", strWhere);
                    cbx.loadDataToCombobox(comboBoxPhuongXa, sqlcommand);
                    //loadDataToCombobox(cbxMaPhuong, sqlcommand);

                }
            }
        }
    }
}
